{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = 'C:/Users/pc/Downloads/4月份日报数据.csv'\n",
    "\n",
    "df = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# file_content中将“用户”等于杨帆、吴冬雪和滕昊的内容过滤掉\n",
    "df = df[~df['用户'].isin(['杨 帆C', '吴 冬雪', '滕 昊'])]\n",
    "\n",
    "df.to_csv('C:/Users/pc/Downloads/4月份日报数据_过滤.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户</th>\n",
       "      <th>岗位角色</th>\n",
       "      <th>职位名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>耿 杰</td>\n",
       "      <td>管理</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>杨 帆C</td>\n",
       "      <td>管理</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>齐 钢</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>徐 云涛</td>\n",
       "      <td>项目</td>\n",
       "      <td>项目经理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>徐 鹏超</td>\n",
       "      <td>产品</td>\n",
       "      <td>产品经理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>李 爽</td>\n",
       "      <td>产品</td>\n",
       "      <td>UE工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>韩 品佳</td>\n",
       "      <td>产品</td>\n",
       "      <td>产品经理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>张 思文</td>\n",
       "      <td>产品</td>\n",
       "      <td>产品经理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>郝 泽霖</td>\n",
       "      <td>产品</td>\n",
       "      <td>产品经理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>刘大 铭B</td>\n",
       "      <td>产品</td>\n",
       "      <td>产品经理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>孙舟 恩琪</td>\n",
       "      <td>UI&amp;Design</td>\n",
       "      <td>UI设计师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>张 文鹤</td>\n",
       "      <td>管理</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>段 笛</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>于 凯奇</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>孔 泽成</td>\n",
       "      <td>算法</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>滕 昊</td>\n",
       "      <td>运维</td>\n",
       "      <td>运维工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>王 思涵</td>\n",
       "      <td>前端</td>\n",
       "      <td>web前端开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>刘 喆</td>\n",
       "      <td>前端</td>\n",
       "      <td>web前端开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>鲁 佳</td>\n",
       "      <td>前端</td>\n",
       "      <td>web前端开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>刘 鑫添</td>\n",
       "      <td>前端</td>\n",
       "      <td>web前端开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>陈 峰高</td>\n",
       "      <td>大数据</td>\n",
       "      <td>大数据开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>高 林</td>\n",
       "      <td>大数据</td>\n",
       "      <td>大数据开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>郑 学文</td>\n",
       "      <td>大数据</td>\n",
       "      <td>大数据开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>郑 文</td>\n",
       "      <td>大数据</td>\n",
       "      <td>大数据开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>徐 辉</td>\n",
       "      <td>大数据</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>周 鹏</td>\n",
       "      <td>大数据</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>杨 添斯</td>\n",
       "      <td>大数据</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>宋 超</td>\n",
       "      <td>算法</td>\n",
       "      <td>算法工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>姜 斌</td>\n",
       "      <td>算法</td>\n",
       "      <td>算法工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>吴 冰川</td>\n",
       "      <td>算法</td>\n",
       "      <td>算法工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>汪 文博</td>\n",
       "      <td>测试</td>\n",
       "      <td>测试工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>张 书译</td>\n",
       "      <td>测试</td>\n",
       "      <td>测试工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>吴 冬雪</td>\n",
       "      <td>测试</td>\n",
       "      <td>测试工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>蒋 伟</td>\n",
       "      <td>Android</td>\n",
       "      <td>Android工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>王 奎</td>\n",
       "      <td>Android</td>\n",
       "      <td>Android工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>康 玉龙</td>\n",
       "      <td>Android</td>\n",
       "      <td>Android工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>钟 继伟</td>\n",
       "      <td>Android</td>\n",
       "      <td>Android工程师</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       用户       岗位角色        职位名称\n",
       "0     耿 杰         管理         NaN\n",
       "1    杨 帆C         管理         NaN\n",
       "2     齐 钢         后端   Java开发工程师\n",
       "3    徐 云涛         项目        项目经理\n",
       "4    徐 鹏超         产品        产品经理\n",
       "5     李 爽         产品       UE工程师\n",
       "6    韩 品佳         产品        产品经理\n",
       "7    张 思文         产品        产品经理\n",
       "8    郝 泽霖         产品        产品经理\n",
       "9   刘大 铭B         产品        产品经理\n",
       "10  孙舟 恩琪  UI&Design       UI设计师\n",
       "11   张 文鹤         管理         NaN\n",
       "12    段 笛         后端   Java开发工程师\n",
       "13   于 凯奇         后端   Java开发工程师\n",
       "14   孔 泽成         算法   Java开发工程师\n",
       "15    滕 昊         运维       运维工程师\n",
       "16   王 思涵         前端  web前端开发工程师\n",
       "17    刘 喆         前端  web前端开发工程师\n",
       "18    鲁 佳         前端  web前端开发工程师\n",
       "19   刘 鑫添         前端  web前端开发工程师\n",
       "20   陈 峰高        大数据    大数据开发工程师\n",
       "21    高 林        大数据    大数据开发工程师\n",
       "22   郑 学文        大数据    大数据开发工程师\n",
       "23    郑 文        大数据    大数据开发工程师\n",
       "24    徐 辉        大数据   Java开发工程师\n",
       "25    周 鹏        大数据   Java开发工程师\n",
       "26   杨 添斯        大数据   Java开发工程师\n",
       "27    宋 超         算法       算法工程师\n",
       "28    姜 斌         算法       算法工程师\n",
       "29   吴 冰川         算法       算法工程师\n",
       "30   汪 文博         测试       测试工程师\n",
       "31   张 书译         测试       测试工程师\n",
       "32   吴 冬雪         测试       测试工程师\n",
       "33    蒋 伟    Android  Android工程师\n",
       "34    王 奎    Android  Android工程师\n",
       "35   康 玉龙    Android  Android工程师\n",
       "36   钟 继伟    Android  Android工程师"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取法定工作日表\n",
    "df_people = pd.read_excel('E:/个人文件夹/Python教程/wukong/Files/workLoad/人员配置.xlsx')\n",
    "\n",
    "df_people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>用户</th>\n",
       "      <th>活动</th>\n",
       "      <th>问题</th>\n",
       "      <th>注释</th>\n",
       "      <th>小时</th>\n",
       "      <th>岗位角色</th>\n",
       "      <th>职位名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>吴 冰川</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #100305: 电池健康时间序列预测算法相关功能开发</td>\n",
       "      <td>电池健康时间序列预测算法需求与方案整理</td>\n",
       "      <td>0.44</td>\n",
       "      <td>算法</td>\n",
       "      <td>算法工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>高 林</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #101959: 大数据组-项目日报-2405月份-数字化平台</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.02</td>\n",
       "      <td>大数据</td>\n",
       "      <td>大数据开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>于 凯奇</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #98539: 模块：灵镜-一级：我的洞察-后端</td>\n",
       "      <td>模块：灵镜-一级：我的洞察-后端</td>\n",
       "      <td>8.29</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>宋 超</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #101965: 大模型调研，ppt编辑</td>\n",
       "      <td>大模型调研，ppt编辑</td>\n",
       "      <td>4.33</td>\n",
       "      <td>算法</td>\n",
       "      <td>算法工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>徐 云涛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>管理 #89427: 项目管理（Smart-D）</td>\n",
       "      <td>项目管理</td>\n",
       "      <td>8.47</td>\n",
       "      <td>项目</td>\n",
       "      <td>项目经理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>齐 钢</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>管理 #87245: 项目管理-Smart-D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.13</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>段 笛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95638: 【汽车数字化平台-OpenAPI】dev环境  OpenAPI...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>段 笛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95632: 【汽车数字化平台-OpenAPI】dev环境 【新增 Open...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>段 笛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95374: 【汽车数字化平台-OpenAPI】dev环境 OpenAPI列...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>蒋 伟</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95577: 【Smart-D】HMI端，前置条件一直保持登录状态，然后进行...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Android</td>\n",
       "      <td>Android工程师</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>800 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             日期    用户    活动  \\\n",
       "0    2024-04-30  吴 冰川  默认活动   \n",
       "1    2024-04-30   高 林  默认活动   \n",
       "2    2024-04-30  于 凯奇  默认活动   \n",
       "3    2024-04-30   宋 超  默认活动   \n",
       "4    2024-04-30  徐 云涛  默认活动   \n",
       "..          ...   ...   ...   \n",
       "795  2024-04-01   齐 钢  默认活动   \n",
       "796  2024-04-01   段 笛  默认活动   \n",
       "797  2024-04-01   段 笛  默认活动   \n",
       "798  2024-04-01   段 笛  默认活动   \n",
       "799  2024-04-01   蒋 伟  默认活动   \n",
       "\n",
       "                                                    问题                   注释  \\\n",
       "0                       研发 #100305: 电池健康时间序列预测算法相关功能开发  电池健康时间序列预测算法需求与方案整理   \n",
       "1                   研发 #101959: 大数据组-项目日报-2405月份-数字化平台                  NaN   \n",
       "2                          研发 #98539: 模块：灵镜-一级：我的洞察-后端     模块：灵镜-一级：我的洞察-后端   \n",
       "3                              研发 #101965: 大模型调研，ppt编辑          大模型调研，ppt编辑   \n",
       "4                             管理 #89427: 项目管理（Smart-D）                 项目管理   \n",
       "..                                                 ...                  ...   \n",
       "795                            管理 #87245: 项目管理-Smart-D                  NaN   \n",
       "796  Bug-ST #95638: 【汽车数字化平台-OpenAPI】dev环境  OpenAPI...                  NaN   \n",
       "797  Bug-ST #95632: 【汽车数字化平台-OpenAPI】dev环境 【新增 Open...                  NaN   \n",
       "798  Bug-ST #95374: 【汽车数字化平台-OpenAPI】dev环境 OpenAPI列...                  NaN   \n",
       "799  Bug-ST #95577: 【Smart-D】HMI端，前置条件一直保持登录状态，然后进行...                  NaN   \n",
       "\n",
       "       小时     岗位角色        职位名称  \n",
       "0    0.44       算法       算法工程师  \n",
       "1    0.02      大数据    大数据开发工程师  \n",
       "2    8.29       后端   Java开发工程师  \n",
       "3    4.33       算法       算法工程师  \n",
       "4    8.47       项目        项目经理  \n",
       "..    ...      ...         ...  \n",
       "795  9.13       后端   Java开发工程师  \n",
       "796  0.50       后端   Java开发工程师  \n",
       "797  0.50       后端   Java开发工程师  \n",
       "798  0.50       后端   Java开发工程师  \n",
       "799  2.00  Android  Android工程师  \n",
       "\n",
       "[800 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#将df_people和df合并\n",
    "df = pd.merge(df, df_people, on='用户', how='left')\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>用户</th>\n",
       "      <th>活动</th>\n",
       "      <th>问题</th>\n",
       "      <th>注释</th>\n",
       "      <th>小时</th>\n",
       "      <th>岗位角色</th>\n",
       "      <th>职位名称</th>\n",
       "      <th>月份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>吴 冰川</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #100305: 电池健康时间序列预测算法相关功能开发</td>\n",
       "      <td>电池健康时间序列预测算法需求与方案整理</td>\n",
       "      <td>0.44</td>\n",
       "      <td>算法</td>\n",
       "      <td>算法工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>高 林</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #101959: 大数据组-项目日报-2405月份-数字化平台</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.02</td>\n",
       "      <td>大数据</td>\n",
       "      <td>大数据开发工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>于 凯奇</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #98539: 模块：灵镜-一级：我的洞察-后端</td>\n",
       "      <td>模块：灵镜-一级：我的洞察-后端</td>\n",
       "      <td>8.29</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>宋 超</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>研发 #101965: 大模型调研，ppt编辑</td>\n",
       "      <td>大模型调研，ppt编辑</td>\n",
       "      <td>4.33</td>\n",
       "      <td>算法</td>\n",
       "      <td>算法工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>徐 云涛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>管理 #89427: 项目管理（Smart-D）</td>\n",
       "      <td>项目管理</td>\n",
       "      <td>8.47</td>\n",
       "      <td>项目</td>\n",
       "      <td>项目经理</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>齐 钢</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>管理 #87245: 项目管理-Smart-D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.13</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>段 笛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95638: 【汽车数字化平台-OpenAPI】dev环境  OpenAPI...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>段 笛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95632: 【汽车数字化平台-OpenAPI】dev环境 【新增 Open...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>段 笛</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95374: 【汽车数字化平台-OpenAPI】dev环境 OpenAPI列...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "      <td>后端</td>\n",
       "      <td>Java开发工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>蒋 伟</td>\n",
       "      <td>默认活动</td>\n",
       "      <td>Bug-ST #95577: 【Smart-D】HMI端，前置条件一直保持登录状态，然后进行...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Android</td>\n",
       "      <td>Android工程师</td>\n",
       "      <td>202404</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>800 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             日期    用户    活动  \\\n",
       "0    2024-04-30  吴 冰川  默认活动   \n",
       "1    2024-04-30   高 林  默认活动   \n",
       "2    2024-04-30  于 凯奇  默认活动   \n",
       "3    2024-04-30   宋 超  默认活动   \n",
       "4    2024-04-30  徐 云涛  默认活动   \n",
       "..          ...   ...   ...   \n",
       "795  2024-04-01   齐 钢  默认活动   \n",
       "796  2024-04-01   段 笛  默认活动   \n",
       "797  2024-04-01   段 笛  默认活动   \n",
       "798  2024-04-01   段 笛  默认活动   \n",
       "799  2024-04-01   蒋 伟  默认活动   \n",
       "\n",
       "                                                    问题                   注释  \\\n",
       "0                       研发 #100305: 电池健康时间序列预测算法相关功能开发  电池健康时间序列预测算法需求与方案整理   \n",
       "1                   研发 #101959: 大数据组-项目日报-2405月份-数字化平台                  NaN   \n",
       "2                          研发 #98539: 模块：灵镜-一级：我的洞察-后端     模块：灵镜-一级：我的洞察-后端   \n",
       "3                              研发 #101965: 大模型调研，ppt编辑          大模型调研，ppt编辑   \n",
       "4                             管理 #89427: 项目管理（Smart-D）                 项目管理   \n",
       "..                                                 ...                  ...   \n",
       "795                            管理 #87245: 项目管理-Smart-D                  NaN   \n",
       "796  Bug-ST #95638: 【汽车数字化平台-OpenAPI】dev环境  OpenAPI...                  NaN   \n",
       "797  Bug-ST #95632: 【汽车数字化平台-OpenAPI】dev环境 【新增 Open...                  NaN   \n",
       "798  Bug-ST #95374: 【汽车数字化平台-OpenAPI】dev环境 OpenAPI列...                  NaN   \n",
       "799  Bug-ST #95577: 【Smart-D】HMI端，前置条件一直保持登录状态，然后进行...                  NaN   \n",
       "\n",
       "       小时     岗位角色        职位名称      月份  \n",
       "0    0.44       算法       算法工程师  202404  \n",
       "1    0.02      大数据    大数据开发工程师  202404  \n",
       "2    8.29       后端   Java开发工程师  202404  \n",
       "3    4.33       算法       算法工程师  202404  \n",
       "4    8.47       项目        项目经理  202404  \n",
       "..    ...      ...         ...     ...  \n",
       "795  9.13       后端   Java开发工程师  202404  \n",
       "796  0.50       后端   Java开发工程师  202404  \n",
       "797  0.50       后端   Java开发工程师  202404  \n",
       "798  0.50       后端   Java开发工程师  202404  \n",
       "799  2.00  Android  Android工程师  202404  \n",
       "\n",
       "[800 rows x 9 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['月份'] = df['日期'].apply(lambda x: x[:4] + x[5:7])\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>月份</th>\n",
       "      <th>岗位角色</th>\n",
       "      <th>小时</th>\n",
       "      <th>用户</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>202404</td>\n",
       "      <td>Android</td>\n",
       "      <td>543.60</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>202404</td>\n",
       "      <td>UI&amp;Design</td>\n",
       "      <td>173.70</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202404</td>\n",
       "      <td>产品</td>\n",
       "      <td>1142.81</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>202404</td>\n",
       "      <td>前端</td>\n",
       "      <td>555.01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>202404</td>\n",
       "      <td>后端</td>\n",
       "      <td>570.80</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>202404</td>\n",
       "      <td>大数据</td>\n",
       "      <td>1340.52</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>202404</td>\n",
       "      <td>测试</td>\n",
       "      <td>405.91</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202404</td>\n",
       "      <td>算法</td>\n",
       "      <td>768.08</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>202404</td>\n",
       "      <td>管理</td>\n",
       "      <td>202.54</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>202404</td>\n",
       "      <td>项目</td>\n",
       "      <td>206.90</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       月份       岗位角色       小时  用户\n",
       "0  202404    Android   543.60   3\n",
       "1  202404  UI&Design   173.70   1\n",
       "2  202404         产品  1142.81   6\n",
       "3  202404         前端   555.01   3\n",
       "4  202404         后端   570.80   3\n",
       "5  202404        大数据  1340.52   7\n",
       "6  202404         测试   405.91   2\n",
       "7  202404         算法   768.08   4\n",
       "8  202404         管理   202.54   1\n",
       "9  202404         项目   206.90   1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_month = df.groupby(['月份','岗位角色'])['小时'].sum()\n",
    "df_month = df_month.reset_index()\n",
    "\n",
    "# 按月份计算每月填写日报的去重人数\n",
    "df_person = df.groupby(['月份','岗位角色'])['用户'].nunique()\n",
    "df_person = df_person.reset_index()\n",
    "\n",
    "# 将df_month和df_person按“月份”和“岗位角色”合并 \n",
    "df_merge = pd.merge(df_month, df_person, on=['月份','岗位角色'], how='left')\n",
    "\n",
    "df_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>月份</th>\n",
       "      <th>岗位角色</th>\n",
       "      <th>小时</th>\n",
       "      <th>用户</th>\n",
       "      <th>负荷度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>202404</td>\n",
       "      <td>Android</td>\n",
       "      <td>543.60</td>\n",
       "      <td>3</td>\n",
       "      <td>1.029545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>202404</td>\n",
       "      <td>UI&amp;Design</td>\n",
       "      <td>173.70</td>\n",
       "      <td>1</td>\n",
       "      <td>0.986932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202404</td>\n",
       "      <td>产品</td>\n",
       "      <td>1142.81</td>\n",
       "      <td>6</td>\n",
       "      <td>1.082206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>202404</td>\n",
       "      <td>前端</td>\n",
       "      <td>555.01</td>\n",
       "      <td>3</td>\n",
       "      <td>1.051155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>202404</td>\n",
       "      <td>后端</td>\n",
       "      <td>570.80</td>\n",
       "      <td>3</td>\n",
       "      <td>1.081061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>202404</td>\n",
       "      <td>大数据</td>\n",
       "      <td>1340.52</td>\n",
       "      <td>7</td>\n",
       "      <td>1.088084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>202404</td>\n",
       "      <td>测试</td>\n",
       "      <td>405.91</td>\n",
       "      <td>2</td>\n",
       "      <td>1.153153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202404</td>\n",
       "      <td>算法</td>\n",
       "      <td>768.08</td>\n",
       "      <td>4</td>\n",
       "      <td>1.091023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>202404</td>\n",
       "      <td>管理</td>\n",
       "      <td>202.54</td>\n",
       "      <td>1</td>\n",
       "      <td>1.150795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>202404</td>\n",
       "      <td>项目</td>\n",
       "      <td>206.90</td>\n",
       "      <td>1</td>\n",
       "      <td>1.175568</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       月份       岗位角色       小时  用户       负荷度\n",
       "0  202404    Android   543.60   3  1.029545\n",
       "1  202404  UI&Design   173.70   1  0.986932\n",
       "2  202404         产品  1142.81   6  1.082206\n",
       "3  202404         前端   555.01   3  1.051155\n",
       "4  202404         后端   570.80   3  1.081061\n",
       "5  202404        大数据  1340.52   7  1.088084\n",
       "6  202404         测试   405.91   2  1.153153\n",
       "7  202404         算法   768.08   4  1.091023\n",
       "8  202404         管理   202.54   1  1.150795\n",
       "9  202404         项目   206.90   1  1.175568"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge['负荷度'] = df_merge['小时'] / (df_merge['用户'] * 8 * 22)\n",
    "\n",
    "df_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>月份</th>\n",
       "      <th>202404</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>岗位角色</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Android</th>\n",
       "      <td>1.029545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UI&amp;Design</th>\n",
       "      <td>0.986932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>产品</th>\n",
       "      <td>1.082206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>前端</th>\n",
       "      <td>1.051155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>后端</th>\n",
       "      <td>1.081061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>大数据</th>\n",
       "      <td>1.088084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>测试</th>\n",
       "      <td>1.153153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>算法</th>\n",
       "      <td>1.091023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>管理</th>\n",
       "      <td>1.150795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>项目</th>\n",
       "      <td>1.175568</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "月份           202404\n",
       "岗位角色               \n",
       "Android    1.029545\n",
       "UI&Design  0.986932\n",
       "产品         1.082206\n",
       "前端         1.051155\n",
       "后端         1.081061\n",
       "大数据        1.088084\n",
       "测试         1.153153\n",
       "算法         1.091023\n",
       "管理         1.150795\n",
       "项目         1.175568"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pivot = df_merge.pivot(index='岗位角色', columns='月份', values='负荷度')\n",
    "\n",
    "df_pivot"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
